package Dao;

import model.Goods;
import model.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import utils.DataSourceUtils;

import java.sql.*;
import java.util.List;

public class UserDao {

    // 检查用户名是否存在
    public boolean isUsernameExist(String username) {
        String sql = "select * from user where username = ?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, username);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    //获取所有用户信息
    public List<User> GetAllUser() throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select id, nickname, username, password, email, create_time, avatar, is_admin AS admin from user";
//                try {
//                    Connection conn = DataSourceUtils.getDataSource().getConnection();
//                    System.out.println("conn对象：" + conn);
//                    System.out.println("当前连接到的数据库是：" + conn.getCatalog());
//                    System.out.println(r.query(sql,new BeanListHandler<User>(User.class)));
//                } catch (Exception e) {
//                    e.printStackTrace();
//                    System.out.println("获取数据库连接时发生异常！");
//                }
        return r.query(sql,new BeanListHandler<User>(User.class));
    }


    // 检查邮箱是否存在
    public boolean isEmailExist(String email) {
        String sql = "select * from user where email = ?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, email);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 添加用户
    public boolean addUser(User user) {
        String sql = "insert into user(nickname, username, password, email, create_time, is_admin, avatar) values(?,?,?,?,?,?,?)";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, user.getNickname());
            stmt.setString(2, user.getUsername());
            stmt.setString(3, user.getPassword());
            stmt.setString(4, user.getEmail());
//            stmt.setTimestamp(5, new java.sql.Timestamp(user.getCreate_time().getTime()));
            java.util.Date utilDate = user.getCreate_time();
            if (utilDate != null) {
                // 将java.util.Date转换为java.sql.Timestamp
                java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(utilDate.getTime());
                stmt.setTimestamp(5, sqlTimestamp); // 设置时间戳参数
            } else {
                // 如果时间为空，可以设置默认值或抛出异常
                stmt.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));
            }
            stmt.setBoolean(6, user.isAdmin());
            stmt.setString(7, user.getAvatar());
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 根据用户名和密码查询用户
    public User selectByUsernamePassword(String username, String password) {
        String sql = "select * from user where username = ? and password = ?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, username);
            stmt.setString(2, password);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setNickname(rs.getString("nickname"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setCreate_time(rs.getTimestamp("create_time"));
                user.setAdmin(rs.getBoolean("is_admin"));
                user.setAvatar(rs.getString("avatar"));
                return user;
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    //获取用户列表
    public List selectUserList(int pageNo, int pageSize) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select * from user limit ?,?";
        return r.query(sql, new BeanListHandler<User>(User.class), (pageNo-1)*pageSize,pageSize );
    }
    //管理员删除用户
    public void delete(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from user where id = ?";
        r.update(sql,id);
    }
    // 更新用户信息
    public boolean updateUser(User user) {
        String sql = "update user set nickname=?, email=?, avatar=? where id=?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, user.getNickname());
            stmt.setString(2, user.getEmail());
            stmt.setString(3, user.getAvatar());
            stmt.setInt(4, user.getId());
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 验证用户密码
    public boolean verifyPassword(int userId, String password) {
        String sql = "select * from user where id = ? and password = ?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, password);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新用户密码
    public boolean updatePassword(int userId, String newPassword) {
        String sql = "update user set password = ? where id = ?";
        try (Connection conn = DataSourceUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, newPassword);
            stmt.setInt(2, userId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

    }
}
